package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.Aluguel;
import model.Hospede;
import model.Quarto;

public class AluguelDao {
	GenericDao gDao = new GenericDao();
	Connection con = new GenericDao().getConnection();

	public boolean insereAluguel(Aluguel aluguel){
		boolean inserido = false;
		try{
			PreparedStatement ps = con.prepareStatement("insert into t_aluguel (fk_cd_hospede,fk_cd_quarto,dt_inicio,dt_fim) values (?,?,?,?)");
			String idHospede = (""+aluguel.getHospede().getID());
			ps.setString(1,idHospede);
			String idQuarto = (""+aluguel.getQuarto().getId());
			ps.setString(2, idQuarto);
			ps.setDate(3, new java.sql.Date(aluguel.getDateInicial().getTime()));
			ps.setDate(4, new java.sql.Date(aluguel.getDataFinal().getTime()));
			ps.execute();
			ps.close();
			inserido = true;
		}catch(Exception e){
			e.printStackTrace();
		}
		return inserido;
	}

	public Aluguel consultaAluguel(Hospede hospede){
		Aluguel aluguel = new Aluguel();
		ResultSet rs = null;
		ResultSet rs1 = null;
		try{
			PreparedStatement ps = con.prepareStatement("select * from t_aluguel where fk_cd_hospede = ?");
			String idHospede = ""+aluguel.getHospede().getID();
			ps.setString(1,idHospede);
			ps.execute();
            //ps.close();
			rs = ps.getResultSet();
			if (rs.next()){
				aluguel.setDateInicial(rs.getDate(2));
				aluguel.setDataFinal(rs.getDate(3));
			}
		} catch (Exception e){
			e.printStackTrace();
		}
		return aluguel;
	}
	
	public List<Aluguel> consultaAluguelHospede(Hospede hospede){
		List<Aluguel> listaAlugueis = new ArrayList<>();
		ResultSet rs = null;

		try{
			PreparedStatement ps = con.prepareStatement("select t_hospede.nm_hospede," +
					" t_quarto.nr_quarto, t_quarto.nr_andar, t_aluguel.dt_inicio, t_aluguel.dt_fim from t_aluguel " +
					"inner join t_hospede on t_aluguel.fk_cd_hospede = t_hospede.pk_cd_hospede " +
					"inner join t_quarto on t_aluguel.fk_cd_quarto = t_quarto.pk_cd_quarto " +
					"where t_hospede.nr_cpf =?" +
					"order by t_quarto.nr_andar, t_quarto.nr_quarto");
			ps.setString(1, hospede.getCpf());			
			ps.execute();
			rs = ps.getResultSet();
			while(rs.next()){		
				Aluguel aux = new Aluguel();
				Hospede auxHosp = new Hospede();
				Quarto auxQuarto = new Quarto();
				auxHosp.setNome(rs.getString(1));
				auxQuarto.setNumeroQuarto(rs.getInt(2));
				auxQuarto.setAndar(rs.getInt(3));
				aux.setHospede(auxHosp);
				aux.setQuarto(auxQuarto);
				aux.setDateInicial(new java.util.Date(rs.getDate(4).getTime()));
				aux.setDataFinal(new java.util.Date(rs.getDate(5).getTime()));
				listaAlugueis.add(aux);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return listaAlugueis;		
	}
	
	public List<Aluguel> consultaAluguelQuarto(Quarto quarto){
		List<Aluguel> listaAlugueis = new ArrayList<>();
		ResultSet rs = null;

		try{
			PreparedStatement ps = con.prepareStatement("select dbo.t_hospede.nm_hospede," +
					" dbo.t_aluguel.dt_inicio, dbo.t_aluguel.dt_fim from dbo.t_aluguel " +
					"inner join dbo.t_hospede on dbo.t_aluguel.fk_cd_hospede = t_hospede.pk_cd_hospede " +
					"inner join dbo.t_quarto on dbo.t_aluguel.fk_cd_quarto = t_quarto.pk_cd_quarto " +
					"where t_quarto.nr_andar = ? and t_quarto.nr_quarto = ? order by t_aluguel.dt_inicio ");
			ps.setInt(1, quarto.getAndar());
			ps.setInt(2,quarto.getNumeroQuarto());
			ps.execute();
			rs = ps.getResultSet();
			while(rs.next()){		
				Aluguel aux = new Aluguel();
				Hospede auxHosp = new Hospede();
				aux.setQuarto(quarto);
				auxHosp.setNome(rs.getString(1));
				aux.setHospede(auxHosp);
				aux.setDateInicial(new java.util.Date(rs.getDate(2).getTime()));
				aux.setDataFinal(new java.util.Date(rs.getDate(3).getTime()));
				listaAlugueis.add(aux);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return listaAlugueis;		
	}

	public boolean atualizaAluguel(Aluguel aluguel){
		boolean editado = false;
		try{
			PreparedStatement ps = con.prepareStatement("update t_aluguel set fk_cd_hospede = ?, dt_inicio = ?," +
					" dt_fim = ?,  where fk_cd_hospede = ?");

			ps.setString(1, aluguel.getHospede().toString());
			ps.setDate(2, (Date) aluguel.getDateInicial());
			ps.setDate(3, (Date) aluguel.getDataFinal());

			ps.execute();
			ps.close();
			editado = true;
		} catch(Exception e){
			e.printStackTrace();
		}
		return editado;
	}

	public boolean deletaAluguel(Hospede hospede){
		boolean deletado = false;
		try{
			PreparedStatement ps = con.prepareStatement("delete t_aluguel where fk_cd_hospede = ?");
			ps.setString(1, hospede.getNome());
			ps.execute();
			ps.close();
			deletado = true;
		} catch(Exception e){
			e.printStackTrace();
		}
		return deletado;
	}
}
